package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Course;
import kr.or.kead.domain.Professor;
import kr.or.kead.domain.ViewCourse;
import kr.or.kead.utils.MySqlCon;

public class DaoCourse implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Course course = (Course) obj;
		String sql = "insert into course values(?,?,?,?,?)";
		PreparedStatement pstmt=null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, course.getCode());
			pstmt.setInt(2, course.getDepart_code());
			pstmt.setString(3, course.getSubject());
			pstmt.setString(4, course.getMaterial());
			pstmt.setInt(5, course.getProf_code());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Course course = (Course) obj;
		String sql = "update course set depart_code=?, subject=?, material=?, prof_code=? where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, course.getDepart_code());
			pstmt.setString(2, course.getSubject());
			pstmt.setString(3, course.getMaterial());
			pstmt.setInt(4, course.getProf_code());
			pstmt.setInt(5, course.getCode());
			pstmt.execute();
		} catch (SQLException e) {
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		String sql = "delete from course where code=?";
		PreparedStatement pstmt=null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		String sql = "select * from view_course_depart_prof";
		ArrayList<Object> courses = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				courses.add(new ViewCourse(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)));
			}
			return courses;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public Object selectTableById(int stdId) {
		Connection con = MySqlCon.getConn();
		String sql = "select code,depart_code,subject, material, prof_code from course where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, stdId);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return new Course(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4),rs.getInt(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		String sql = "select code, subject from course";
		ArrayList<String> courses = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) courses.add(rs.getInt(1)+" : "+rs.getString(2));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return courses;
		
	}

	@Override
	public Object selectTableByEmail(String email) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int updataTableByEmail(String pass, String email) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public ArrayList<String> selectTableAllNames() {
		// TODO Auto-generated method stub
		return null;
	}
	public int selectMaxCode(int depart){
		Connection con = MySqlCon.getConn();
		String sql = "select max(code) from course where depart_code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, depart);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	public ArrayList<String> selectTableAllListByCode(int prof_code) {
		Connection con = MySqlCon.getConn();
		String sql = "select code, subject from course where prof_code=?";
		ArrayList<String> courses = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, prof_code);
			rs = pstmt.executeQuery();
			while (rs.next()) courses.add(rs.getInt(1)+" : "+rs.getString(2));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return courses;
		
	}

}
